﻿using System.Linq;
using System.Data.Linq;
using UniMana.Data;
using System;
using UniMana.Utils.Controls;
using UniMana.Utils.Data;
namespace UniMana.Data
{
    public static class Training_Session_Subject_Class_RoomData_Chi
    {
        /// <summary>
        /// Lấy dữ liệu
        /// </summary>
        /// <param name="session_Subject_Class_Room"></param>
        /// <param name="course_SchoolYear_Term"></param>
        /// <param name="subject_Class"></param>
        /// <param name="term"></param>
        /// <param name="schoolyear"></param>
        /// <returns></returns>
        public static IQueryable
            GetByTerm_SchoolYear_SubjectClass(
                this Table<Training_Session_Subject_Class_Room> session_Subject_Class_Room,
                Table<Catalogue_Course_SchoolYear_Term> course_SchoolYear_Term,
                int subject_Class,
                int term,
                int schoolyear)
        {
            var IQuery = from se_sj_cr in session_Subject_Class_Room
                         where
                            se_sj_cr.Catalogue_SchoolYearKey == schoolyear &&
                           se_sj_cr.Catalogue_TermKey == term &&
                           se_sj_cr.RegistTime > SysDateTime.Now &&
                           se_sj_cr.Training_Subject_ClassKey == subject_Class &&
                           (se_sj_cr.Status == false || se_sj_cr.Status == null)
                         select se_sj_cr;
            return IQuery;
        }
        public static bds m_bds = new bds();
        /// <summary>
        /// xóa thời gian của lớp học phần
        /// </summary>
        /// <param name="session_Subject_Class_Room"></param>
        /// <param name="subject_Class"></param>
        public static void
            DeleteByTerm_SchoolYear_SubjectClass(
                this Table<Training_Session_Subject_Class_Room> session_Subject_Class_Room,
                int subject_Class)
        {
            var IQuery = from se_sj_cr in session_Subject_Class_Room
                         where se_sj_cr.Training_Subject_ClassKey == subject_Class &&
                           se_sj_cr.RegistTime > SysDateTime.Now &&
                           (se_sj_cr.Status == false || se_sj_cr.Status == null)
                         select se_sj_cr;
            m_bds.DataSource = IQuery;
            while (m_bds.Count > 0)
            {
                m_bds.RemoveAt(0);
            }
        }
        /// <summary>
        /// xóa giảng viên
        /// </summary>
        /// <param name="session_Subject_Class_Room"></param>
        /// <param name="subject_Class"></param>
        public static void
            DeleteByTerm_SchoolYear_SubjectClass_Personnel(
                this Table<Training_Session_Subject_Class_Room> session_Subject_Class_Room,
                int subject_Class,
                string personnel)
        {
            var IQuery = from se_sj_cr in session_Subject_Class_Room
                         where se_sj_cr.Training_Subject_ClassKey == subject_Class &&
                           se_sj_cr.RegistTime > SysDateTime.Now &&
                           se_sj_cr.Personnel_PersonnelKey.ToString() == personnel &&
                           (se_sj_cr.Status == false || se_sj_cr.Status == null)
                         select se_sj_cr;
            m_bds.DataSource = IQuery;
            while (m_bds.Count > 0)
            {
                m_bds.RemoveAt(0);
            }
        }
        /// <summary>
        /// Lấy môn hoc
        /// </summary>
        /// <param name="session_Subject_Class_Room"></param>
        /// <param name="subject_Class"></param>
        /// <returns></returns>
        public static IQueryable
            GetSubject_ClassBySubjectClass(
                this Table<Training_Session_Subject_Class_Room> session_Subject_Class_Room,
                int subject_Class)
        {
            var IQuery = from bien1 in session_Subject_Class_Room
                         where
                           bien1.Training_Subject_ClassKey == subject_Class
                         select new
                         {
                             bien1.Catalogue_SchoolYearKey,
                             bien1.Catalogue_TermKey,
                             bien1.Lessons,
                             bien1.Note,
                             bien1.Personnel_PersonnelKey,
                             bien1.RegistTime,
                             bien1.Status,
                             bien1.Training_Class_RoomKey,
                             bien1.Training_SessionKey,
                             bien1.Training_Subject_ClassKey,
                             bien1.Training_WeekDayKey
                         };
            var Query = from bien2 in session_Subject_Class_Room
                        join k in IQuery on bien2.Catalogue_SchoolYearKey equals k.Catalogue_SchoolYearKey
                        where bien2.Training_WeekDayKey == k.Training_WeekDayKey &&
                                bien2.Training_SessionKey == k.Training_SessionKey &&
                                bien2.Training_Class_RoomKey == k.Training_Class_RoomKey &&
                                bien2.RegistTime == k.RegistTime &&
                                bien2.Personnel_PersonnelKey == k.Personnel_PersonnelKey &&
                                bien2.Catalogue_TermKey == k.Catalogue_TermKey &&

                                bien2.Training_Subject_ClassKey != subject_Class
                        select new { Subject_ClassKey = Convert.ToInt16(bien2.Training_Subject_ClassKey.ToString()) };
            return Query.Distinct();
        }
        /// <summary>
        /// cập nhật thời gian giảng viên
        /// </summary>
        /// <param name="session_Subject_Class_Room"></param>
        /// <param name="personnel_Session"></param>
        /// <param name="subject_Class"></param>
        public static void
            Setpersonnel_Session(
                this Table<Training_Session_Subject_Class_Room> session_Subject_Class_Room,
                Table<Personnel_Personnel_Session> personnel_Session,
                int subject_Class)
        {
            var IQuery = from se_sj_cr in session_Subject_Class_Room
                         where
                           se_sj_cr.Training_Subject_ClassKey == subject_Class &&
                           se_sj_cr.RegistTime > SysDateTime.Now 
                         select new
                         {
                             se_sj_cr.Catalogue_SchoolYearKey,
                             se_sj_cr.Catalogue_TermKey,
                             se_sj_cr.Lessons,
                             se_sj_cr.Note,
                             se_sj_cr.Personnel_PersonnelKey,
                             se_sj_cr.RegistTime,
                             se_sj_cr.Status,
                             se_sj_cr.Training_Class_RoomKey,
                             se_sj_cr.Training_SessionKey,
                             se_sj_cr.Training_Subject_ClassKey,
                             se_sj_cr.Training_WeekDayKey
                         };
            var Query = from p_se in personnel_Session
                        join se_sj_cr in IQuery on p_se.Personnel_PersonnelKey equals se_sj_cr.Personnel_PersonnelKey
                        where   p_se.RegistTime == se_sj_cr.RegistTime &&
                                p_se.Status == true &&
                                p_se.Training_SessionKey == se_sj_cr.Training_SessionKey &&
                                p_se.Training_WeekDayKey == se_sj_cr.Training_WeekDayKey
                        select p_se;
            foreach (var item in Query)
            {
                Personnel_Personnel_Session_ScheduleInfo info =
                    new Personnel_Personnel_Session_ScheduleInfo(item.Personnel_PersonnelKey.ToString(),
                       Convert.ToInt16(item.Personnel_WorkTypeKey),
                       Convert.ToDateTime(item.RegistTime),
                       Convert.ToInt16(item.Training_SessionKey),
                       Convert.ToInt16(item.Training_WeekDayKey));
                if (info != null)
                {
                    info.Properties.Status = false;
                    info.Save();
                }
            }
        }

        public static int
            GetSubjectNumberpass_By_SubjectClass(
                this Table<Training_Session_Subject_Class_Room> session_Subject_Class_Room,
                int subject_Class)
        {
            var Query = from se_sj_cr in session_Subject_Class_Room
                        where se_sj_cr.Training_Subject_ClassKey == subject_Class &&
                              se_sj_cr.Status == false
                        group se_sj_cr by new
                        {
                            se_sj_cr.Training_Subject_ClassKey
                        }
                            into se_sj_crgroup
                            select new
                                {
                                    TotalPast = se_sj_crgroup.Count()
                                };
            var Row = Query.FirstOrDefault();
            if (Row != null)
            {
                return Convert.ToInt16(Query.Take(1));
            }
            else return 0;
        }
        #region [An]
        /// <summary>
        /// Lấy lịch khóa biểu với của giảng viên
        /// </summary>
        /// <param name="n_tsscr"></param>
        /// <param name="n_ts"></param>
        /// <param name="n_tsc"></param>
        /// <param name="n_pl"></param>
        /// <param name="n_tw"></param>
        /// <param name="n_tcr"></param>
        /// <param name="n_tsub"></param>
        /// <param name="m_dStart"></param>
        /// <param name="m_dEnd"></param>
        /// <param name="m_strPersonKey"></param>
        /// <returns></returns>

        public static IQueryable GetDataSchedulePersonWithTime(this Table<Training_Session_Subject_Class_Room> n_tsscr, Table<Training_Session> n_ts, Table<Training_Subject_Class> n_tsc, Table<Personnel_Personnel> n_pl, Table<Training_WeekDay> n_tw, Table<Training_Class_Room> n_tcr, Table<Training_Subject> n_tsub, DateTime m_dStart, DateTime m_dEnd, string m_strPersonKey)
        {
            var values = from t in n_tsscr
                         join t0 in n_ts on t.Training_SessionKey equals t0.Training_SessionKey
                         join t1 in n_tsc on t.Training_Subject_ClassKey equals t1.Training_Subject_ClassKey
                         join t2 in n_pl on t.Personnel_PersonnelKey equals t2.Personnel_PersonnelKey
                         join t3 in n_tw on t.Training_WeekDayKey equals t3.Training_WeekDayKey
                         join t4 in n_tcr on t.Training_Class_RoomKey equals t4.Training_Class_RoomKey
                         join t5 in n_tsub on new { Training_SubjectKey = Convert.ToInt32(t1.Training_SubjectKey) } equals new { Training_SubjectKey = t5.Training_SubjectKey }
                         where t.RegistTime >= m_dStart
                         where t.RegistTime <= m_dEnd
                         orderby
                           t.RegistTime
                         select new
                         {
                             Training_SessionKey = t.Training_SessionKey,
                             SessionName = t0.Name,
                             SessionStartTime = t0.StartTime,
                             SessionFinishTime = t0.FinishTime,
                             Training_Subject_ClassKey = t.Training_Subject_ClassKey,
                             ClassID = t1.ID,
                             ClassName = t1.Name,
                             Training_SubjectKey = t1.Training_SubjectKey,
                             SubjectID = t5.ID,
                             SubjectName = t5.Name,
                             Credit = t5.TotalCredits,
                             Training_Class_RoomKey = t.Training_Class_RoomKey,
                             RoomID = t4.ID,
                             RoomName = t4.Name,
                             t.Catalogue_TermKey,
                             t.Catalogue_SchoolYearKey,
                             Personnel_PersonnelKey = t.Personnel_PersonnelKey,
                             PersonID = t2.ID,
                             PersonLastName = t2.LastName,
                             PersonName = t2.Name,
                             t.RegistTime,
                             Training_WeekDayKey = t.Training_WeekDayKey,
                             WeekDayName = t3.Name,
                         };
            if (m_strPersonKey != "")
            {
                values = values.Where(tb => tb.Personnel_PersonnelKey.ToString() == m_strPersonKey);
            }
            return values.Distinct();
        }

        /// <summary>
        /// Lấy lịch khóa biểu của phòng
        /// </summary>
        /// <param name="n_tsscr"></param>
        /// <param name="n_ts"></param>
        /// <param name="n_tsc"></param>
        /// <param name="n_pl"></param>
        /// <param name="n_tw"></param>
        /// <param name="n_tcr"></param>
        /// <param name="n_tsub"></param>
        /// <param name="m_dStart"></param>
        /// <param name="m_dEnd"></param>
        /// <param name="m_iRoomKey"></param>
        /// <returns></returns>

        public static IQueryable GetDataScheduleRoomWithTime(this Table<Training_Session_Subject_Class_Room> n_tsscr, Table<Training_Session> n_ts, Table<Training_Subject_Class> n_tsc, Table<Personnel_Personnel> n_pl, Table<Training_WeekDay> n_tw, Table<Training_Class_Room> n_tcr, Table<Training_Subject> n_tsub, DateTime m_dStart, DateTime m_dEnd, int m_iRoomKey)
        {
            var values = from t in n_tsscr
                         join t0 in n_ts on t.Training_SessionKey equals t0.Training_SessionKey
                         join t1 in n_tsc on t.Training_Subject_ClassKey equals t1.Training_Subject_ClassKey
                         join t2 in n_pl on t.Personnel_PersonnelKey equals t2.Personnel_PersonnelKey
                         join t3 in n_tw on t.Training_WeekDayKey equals t3.Training_WeekDayKey
                         join t4 in n_tcr on t.Training_Class_RoomKey equals t4.Training_Class_RoomKey
                         join t5 in n_tsub on new { Training_SubjectKey = Convert.ToInt32(t1.Training_SubjectKey) } equals new { Training_SubjectKey = t5.Training_SubjectKey }
                         where t.RegistTime >= m_dStart
                         where t.RegistTime <= m_dEnd
                         where t.Training_Class_RoomKey == m_iRoomKey
                         orderby
                           t.RegistTime
                         select new
                         {
                             Training_SessionKey = t.Training_SessionKey,
                             SessionName = t0.Name,
                             SessionStartTime = t0.StartTime,
                             SessionFinishTime = t0.FinishTime,
                             Training_Subject_ClassKey = t.Training_Subject_ClassKey,
                             ClassID = t1.ID,
                             ClassName = t1.Name,
                             Training_SubjectKey = t1.Training_SubjectKey,
                             SubjectID = t5.ID,
                             SubjectName = t5.Name,
                             Credit = t5.TotalCredits,
                             Training_Class_RoomKey = t.Training_Class_RoomKey,
                             RoomID = t4.ID,
                             RoomName = t4.Name,
                             t.Catalogue_TermKey,
                             t.Catalogue_SchoolYearKey,
                             Personnel_PersonnelKey = t.Personnel_PersonnelKey,
                             PersonID = t2.ID,
                             PersonLastName = t2.LastName,
                             PersonName = t2.Name,
                             t.RegistTime,
                             Training_WeekDayKey = t.Training_WeekDayKey,
                             WeekDayName = t3.Name,
                         };
            return values.Distinct();
        }

        /// <summary>
        /// Lấy lịch khóa biểu của sinh viên
        /// </summary>
        /// <param name="n_tsscr"></param>
        /// <param name="n_ts"></param>
        /// <param name="n_tsc"></param>
        /// <param name="n_pl"></param>
        /// <param name="n_tw"></param>
        /// <param name="n_tcr"></param>
        /// <param name="n_tsub"></param>
        /// <param name="n_tssc"></param>
        /// <param name="m_dStart"></param>
        /// <param name="m_dEnd"></param>
        /// <param name="m_strStudentKey"></param>
        /// <returns></returns>

        public static IQueryable GetDataScheduleStudentWithTime(this Table<Training_Session_Subject_Class_Room> n_tsscr, Table<Training_Session> n_ts, Table<Training_Subject_Class> n_tsc, Table<Personnel_Personnel> n_pl, Table<Training_WeekDay> n_tw, Table<Training_Class_Room> n_tcr, Table<Training_Subject> n_tsub, Table<Training_Student_Subject_Class> n_tssc, DateTime m_dStart, DateTime m_dEnd, string m_strStudentKey)
        {
            var values = from t in n_tsscr
                         join t0 in n_ts on t.Training_SessionKey equals t0.Training_SessionKey
                         join t1 in n_tsc on t.Training_Subject_ClassKey equals t1.Training_Subject_ClassKey
                         join t2 in n_pl on t.Personnel_PersonnelKey equals t2.Personnel_PersonnelKey
                         join t3 in n_tw on t.Training_WeekDayKey equals t3.Training_WeekDayKey
                         join t4 in n_tcr on t.Training_Class_RoomKey equals t4.Training_Class_RoomKey
                         join t5 in n_tsub on new { Training_SubjectKey = Convert.ToInt32(t1.Training_SubjectKey) } equals new { Training_SubjectKey = t5.Training_SubjectKey }
                         join t6 in n_tssc on t.Training_Subject_ClassKey equals t6.Training_Subject_ClassKey
                         where t.RegistTime >= m_dStart
                         where t.RegistTime <= m_dEnd
                         where t6.Training_StudentKey.ToString() == m_strStudentKey
                         orderby
                           t.RegistTime
                         select new
                         {
                             Training_SessionKey = t.Training_SessionKey,
                             SessionName = t0.Name,
                             SessionStartTime = t0.StartTime,
                             SessionFinishTime = t0.FinishTime,
                             Training_Subject_ClassKey = t.Training_Subject_ClassKey,
                             ClassID = t1.ID,
                             ClassName = t1.Name,
                             Training_SubjectKey = t1.Training_SubjectKey,
                             SubjectID = t5.ID,
                             SubjectName = t5.Name,
                             Credit = t5.TotalCredits,
                             Training_Class_RoomKey = t.Training_Class_RoomKey,
                             RoomID = t4.ID,
                             RoomName = t4.Name,
                             t.Catalogue_TermKey,
                             t.Catalogue_SchoolYearKey,
                             Personnel_PersonnelKey = t.Personnel_PersonnelKey,
                             PersonID = t2.ID,
                             PersonLastName = t2.LastName,
                             PersonName = t2.Name,
                             t.RegistTime,
                             Training_WeekDayKey = t.Training_WeekDayKey,
                             WeekDayName = t3.Name,
                         };
            return values.Distinct();
        }

        /// <summary>
        /// Lấy lịch thời khóa biểu của khoa
        /// </summary>
        /// <param name="nTraining_Session_Subject_Class_Room"></param>
        /// <param name="nTraining_Subject_Open"></param>
        /// <param name="nCatalogue_Major"></param>
        /// <param name="nTraining_Subject_Classs"></param>
        /// <param name="nTraining_Session"></param>
        /// <param name="nTraining_Subject"></param>
        /// <param name="nTraining_Class_Room"></param>
        /// <param name="nPersonnel_Personnel"></param>
        /// <param name="nTraining_WeekDay"></param>
        /// <param name="m_iCatalogue_DepartmentKey"></param>
        /// <param name="m_iCatalogue_CourseKey"></param>
        /// <param name="m_iCatalogue_LevelKey"></param>
        /// <param name="m_iCatalogue_TrainingTypeKey"></param>
        /// <param name="m_dStart"></param>
        /// <param name="m_dEnd"></param>
        /// <returns></returns>

        public static IQueryable GetGetDataScheduleDepartment(this Table<Training_Session_Subject_Class_Room> nTraining_Session_Subject_Class_Room,
            Table<Training_TrainingProgram_Detail> nTraining_TrainingProgram_Detail,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Training_Subject_Class> nTraining_Subject_Classs,
            Table<Training_Session> nTraining_Session,
            Table<Training_Subject> nTraining_Subject,
            Table<Training_Class_Room> nTraining_Class_Room,
            Table<Personnel_Personnel> nPersonnel_Personnel,
            Table<Training_WeekDay> nTraining_WeekDay,
            int m_iCatalogue_DepartmentKey,
            DateTime m_dStart,
            DateTime m_dEnd)
        {
            var subjectDepartment = from t in nTraining_TrainingProgram_Detail
                                    join t0 in nTraining_TrainingProgram on t.Training_TrainingProgramKey equals t0.Training_TrainingProgramKey
                                    join t1 in nTraining_Subject on t.Training_SubjectKey equals t1.Training_SubjectKey
                                    where
                                      t1.Catalogue_DepartmentKey == m_iCatalogue_DepartmentKey
                                    select new
                                    {
                                        t1.Catalogue_DepartmentKey,
                                        t0.Catalogue_LevelKey,
                                        t0.Catalogue_TrainingTypeKey,
                                        t0.Catalogue_CourseKey,
                                        Training_SubjectKey = (System.Int32?)t.Training_SubjectKey
                                    };

            var subjectSchedule = (from t in nTraining_Session_Subject_Class_Room
                                   join t0 in nTraining_Subject_Classs on t.Training_Subject_ClassKey equals t0.Training_Subject_ClassKey
                                   join t1 in nTraining_Session on t.Training_SessionKey equals t1.Training_SessionKey
                                   join t2 in nTraining_Subject on new { Training_SubjectKey = Convert.ToInt32(t0.Training_SubjectKey) } equals new { Training_SubjectKey = t2.Training_SubjectKey }
                                   join t3 in nTraining_Class_Room on t.Training_Class_RoomKey equals t3.Training_Class_RoomKey
                                   join t4 in nPersonnel_Personnel on t.Personnel_PersonnelKey equals t4.Personnel_PersonnelKey
                                   join t5 in nTraining_WeekDay on t.Training_WeekDayKey equals t5.Training_WeekDayKey
                                   where t.RegistTime >= m_dStart &&
                                   t.RegistTime <= m_dEnd
                                   select new
                                   {
                                       Training_SessionKey = t.Training_SessionKey,
                                       SessionName = t1.Name,
                                       SessionStartTime = t1.StartTime,
                                       SessionFinishTime = t1.FinishTime,
                                       Training_Subject_ClassKey = t.Training_Subject_ClassKey,
                                       ClassID = t0.ID,
                                       ClassName = t0.Name,
                                       Training_SubjectKey = t0.Training_SubjectKey,
                                       SubjectID = t2.ID,
                                       SubjectName = t2.Name,
                                       Credit = t2.TotalCredits,
                                       Training_Class_RoomKey = t.Training_Class_RoomKey,
                                       RoomID = t3.ID,
                                       RoomName = t3.Name,
                                       t.Catalogue_TermKey,
                                       t.Catalogue_SchoolYearKey,
                                       Personnel_PersonnelKey = t.Personnel_PersonnelKey,
                                       PersonID = t4.ID,
                                       PersonLastName = t4.LastName,
                                       PersonName = t4.Name,
                                       t.RegistTime,
                                       Training_WeekDayKey = t.Training_WeekDayKey,
                                       WeekDayName = t5.Name
                                   }).Distinct();

            var schedule = from t in subjectSchedule
                           where (from t1 in subjectDepartment
                                  select t1.Training_SubjectKey).Contains(t.Training_SubjectKey)
                           select t;

            return schedule.Distinct();
        }

        /// <summary>
        /// Kiểm tra thời gian trùng với thời gian giảng viên đã đăng ký
        /// </summary>
        /// <param name="nTraining_Session_Subject_Class_Room"></param>
        /// <param name="m_strPersonKey"></param>
        /// <param name="m_iSession"></param>
        /// <param name="m_dRegist"></param>
        /// <returns></returns>

        public static IQueryable CheckInsertOfPersonnal(this Table<Training_Session_Subject_Class_Room> nTraining_Session_Subject_Class_Room,
            string m_strPersonKey,
            int m_iSession,
            DateTime m_dRegist)
        {
            // kiem tra du lieu co trung khi them moi buoi giang
            var values = from t in nTraining_Session_Subject_Class_Room
                         where
                           (t.Lessons != 0 || t.Lessons == null) &&
                           t.Training_SessionKey == m_iSession &&
                           t.Personnel_PersonnelKey.ToString() == m_strPersonKey &&
                           t.RegistTime == m_dRegist
                         select new
                         {
                             t.Personnel_PersonnelKey,
                             t.Training_Subject_ClassKey,
                             t.Training_Class_RoomKey,
                             t.RegistTime,
                             t.Training_WeekDayKey,
                             t.Training_SessionKey,
                             t.Lessons
                         };
            return values;
        }

        /// <summary>
        /// Lấy danh sách giảng viên và lớp học phần trong tháng
        /// </summary>
        /// <param name="nTraining_Session_Subject_Class_Room"></param>
        /// <param name="nTraining_Subject_Class"></param>
        /// <param name="nTraining_Subject"></param>
        /// <param name="nPersonnel_Personnel"></param>
        /// <returns></returns>

        public static IQueryable GetPersonnalSubjectClass(this Table<Training_Session_Subject_Class_Room> nTraining_Session_Subject_Class_Room,
            Table<Training_Subject_Class> nTraining_Subject_Class,
            Table<Training_Subject> nTraining_Subject,
            Table<Personnel_Personnel> nPersonnel_Personnel,
            int m_iMonth,
            int m_iYear)
        {
            var values = from t in nTraining_Session_Subject_Class_Room
                         join t0 in nTraining_Subject_Class on t.Training_Subject_ClassKey equals t0.Training_Subject_ClassKey
                         join t1 in nTraining_Subject on t0.Training_SubjectKey equals t1.Training_SubjectKey
                         join t2 in nPersonnel_Personnel on t.Personnel_PersonnelKey equals t2.Personnel_PersonnelKey
                         where t.RegistTime.Month == m_iMonth &&
                         t.RegistTime.Year == m_iYear
                         select new
                         {
                             t.Personnel_PersonnelKey,
                             t2.Catalogue_DegreeKey,
                             t0.Training_SubjectKey,
                             t.Training_Subject_ClassKey,
                             t1.Credits1,
                             t1.Credits2,
                             t1.Credits3,
                             t1.Credits4,
                             LessonsAll = 0,
                             Training_Class = ""
                         };
            return values.Distinct();
        }

        /// <summary>
        /// Đếm số tiết đã học của giảng viên giảng lớp học phần
        /// </summary>
        /// <param name="nTraining_Session_Subject_Class_Room"></param>
        /// <param name="m_iTraining_Subject_ClassKey"></param>
        /// <param name="m_iPersonnel_PersonnelKey"></param>
        /// <returns></returns>
        /// 
        public static int ReturnNumberLessons(this Table<Training_Session_Subject_Class_Room> nTraining_Session_Subject_Class_Room,
            int m_iTraining_Subject_ClassKey,
            string m_iPersonnel_PersonnelKey)
        {
            var values = from t in
                             (from t in nTraining_Session_Subject_Class_Room
                              where
                                t.Training_Subject_ClassKey == m_iTraining_Subject_ClassKey &&
                                t.Personnel_PersonnelKey.ToString() == m_iPersonnel_PersonnelKey
                              select new
                              {
                                  t.Lessons,
                                  Dummy = "x"
                              })
                         group t by new { t.Dummy } into g
                         select new
                         {
                             sumL = (System.Int32?)g.Sum(p => p.Lessons)
                         };
            try
            {
                return int.Parse(values.FirstOrDefault().sumL.ToString());
            }
            catch { return 0; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="nTraining_Session_Subject_Class_Room"></param>
        /// <param name="m_iTraining_Subject_ClassKey"></param>
        /// <param name="m_iPersonnel_PersonnelKey"></param>
        /// <returns></returns>

        public static int ReturnNumberSessions(this Table<Training_Session_Subject_Class_Room> nTraining_Session_Subject_Class_Room,
            int m_iTraining_Subject_ClassKey,
            string m_iPersonnel_PersonnelKey)
        {
            var values = from t in nTraining_Session_Subject_Class_Room
                         where
                           t.Lessons != null
                         group t by new
                         {
                             t.Training_Subject_ClassKey,
                             t.Personnel_PersonnelKey
                         } into g
                         where g.Key.Training_Subject_ClassKey == m_iTraining_Subject_ClassKey &&
                           Convert.ToString(g.Key.Personnel_PersonnelKey) == m_iPersonnel_PersonnelKey
                         select new
                         {
                             countL = (Int64?)g.Count()
                         };
            try
            {
                return int.Parse(values.FirstOrDefault().countL.ToString());
            }
            catch { return 0; }
        }

        /// <summary>
        /// Kiểm tra thời gian trùng để đăng ký thêm buổi giảng
        /// </summary>
        /// <param name="nTraining_Session_Subject_Class_Room"></param>
        /// <param name="m_iSession"></param>
        /// <param name="m_iRoom"></param>
        /// <param name="m_dRegist"></param>
        /// <returns></returns>

        public static IQueryable CheckInsert(this Table<Training_Session_Subject_Class_Room> nTraining_Session_Subject_Class_Room,
            int m_iSession,
            int m_iRoom,
            DateTime m_dRegist)
        {
            // kiem tra du lieu co trung khi them moi buoi giang
            var values = from t in nTraining_Session_Subject_Class_Room
                         where
                           (t.Lessons != 0 || t.Lessons == null) &&
                           t.Training_SessionKey == m_iSession &&
                           t.Training_Class_RoomKey == m_iRoom &&
                           t.RegistTime == m_dRegist
                         select new
                         {
                             t.Personnel_PersonnelKey,
                             t.Training_Subject_ClassKey,
                             t.Training_Class_RoomKey,
                             t.RegistTime,
                             t.Training_WeekDayKey,
                             t.Training_SessionKey,
                             t.Lessons
                         };
            return values;
        }

        /// <summary>
        /// Lấy tất cả dữ liệu trong tháng
        /// </summary>
        /// <param name="nTraining_Session_Subject_Class_Room"></param>
        /// <returns></returns>

        public static IQueryable GetAllData(this Table<Training_Session_Subject_Class_Room> nTraining_Session_Subject_Class_Room,
            Table<Training_Subject_Class> nTraining_Subject_Class,
            Table<Training_Subject> nTraining_Subject,
            Table<Personnel_Personnel> nPersonnel_Personnel,
            Table<Catalogue_Degree> nCatalogue_Degree,
            Table<Training_Class_Room> nTraining_Class_Room,
            int m_iMonth,
            int m_iYear)
        {
            var values = from t in nTraining_Session_Subject_Class_Room
                         join t1 in nTraining_Class_Room on t.Training_Class_RoomKey equals t1.Training_Class_RoomKey
                         where t.RegistTime.Month == m_iMonth &&
                         t.RegistTime.Year == m_iYear
                         select new
                         {
                             t.Training_SessionKey,
                             t.Training_Subject_ClassKey,
                             t.Training_Class_RoomKey,
                             t.Catalogue_TermKey,
                             t.Catalogue_SchoolYearKey,
                             t.Personnel_PersonnelKey,
                             t.RegistTime,
                             t.Training_WeekDayKey,
                             t.Lessons,
                             t.Status,
                             t.Note,
                             RoomID = t1.ID
                         };

            var subjectclass = from t in nTraining_Subject_Class
                               join t1 in nTraining_Subject on t.Training_SubjectKey equals t1.Training_SubjectKey
                               select new
                               {
                                   t.Training_Subject_ClassKey,
                                   ClassID = t.ID,
                                   ClassName = t.Name,
                                   t.Training_SubjectKey,
                                   SubjectID = t1.ID,
                                   SubjectName = t1.Name,
                                   t1.Credits1,
                                   t1.Credits2,
                                   t1.Credits3,
                                   t1.Credits4,
                               };

            var lecturer = from t in nPersonnel_Personnel
                           join t1 in nCatalogue_Degree on t.Catalogue_DegreeKey equals t1.Catalogue_DegreeKey
                           select new
                           {
                               t.Personnel_PersonnelKey,
                               t.Catalogue_DegreeKey,
                               DegreeName = t1.Name,
                               PersonName = t.LastName + " " + t.Name
                           };

            var result = from t in values
                         join t1 in subjectclass on t.Training_Subject_ClassKey equals t1.Training_Subject_ClassKey
                         join t2 in lecturer on t.Personnel_PersonnelKey equals t2.Personnel_PersonnelKey
                         select new
                         {
                             t.Personnel_PersonnelKey,
                             t2.Catalogue_DegreeKey,
                             t1.Training_SubjectKey,
                             t1.SubjectID,
                             t1.SubjectName,
                             t1.Credits1,
                             t1.Credits2,
                             t1.Credits3,
                             t1.Credits4,
                             t.Training_Subject_ClassKey,
                             t1.ClassID,
                             t1.ClassName,
                             t.Training_Class_RoomKey,
                             t.RegistTime,
                             t.Training_WeekDayKey,
                             t.Training_SessionKey,
                             t.Lessons,
                             t.Status,
                             t.Note,
                             t.RoomID,
                             //Training_Class = ""
                             //t.Catalogue_TermKey,
                             //t.Catalogue_SchoolYearKey,                            
                         };
            return result.Distinct();
        }
        #endregion
    }
}

